import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Formatter;
import java.util.Scanner;
//import Data.Patient;


/**
 * This Query Class must be used with a localhost with the appropriate database. I have the database on my localhost and will demo the code. 
 * As a result, the code will not work as it needs a database which you may not have and we do not know how to send to you. If you like you can 
 * install xampp (windows) mamp (mac) and it will set up a localhost complete with phpmyadmin where the program can be tested. 
 * @author Daranhdara Hun
 *
 */
public class MyQuery 
{


	/**
	 * --------------------------------------------------------------------------------------------------------------------
	 * Use this method to search for a patient by full name
	 * It will return a very long string with all user attribute in the following order
	 * 1 = password		9 = name
	 * 2 = username		10 = address
	 * 3 = pharmacy		11 = email
	 * 4 = birthdate			12 = suspend
	 * 5 = cellphone	13 = ssn
	 * 6 = phone		
	 * 7 = insurance
	 * 8 = gender			
	 * After all 13 attributes are completed, the next 13 will be the attributes of the next 
	 * patient matching the name criteria.		 
	 * @param name
	 * @return ArrayList<User> 
	 * @throws Exception
	 */
	public ArrayList<User> searchUserByName(String name) throws Exception
	{
		System.out.println("SELECT * FROM `patient` WHERE `name` LIKE " + "'"+name+"'");
		//create the query
		PreparedStatement statement = connect().prepareStatement("SELECT * FROM `patient` WHERE `name` LIKE " + "'"+name+"' LIMIT 0 , 30");

		//Creating a variable to execute query
		ResultSet result = statement.executeQuery();
		System.out.println(result);
		// constructs a new empty ArrayList  

		ArrayList<User> patientInfo = new ArrayList<User>();

		while(result.next())
		{
			User tempUser = createUser(result.getString(2));
			patientInfo.add(tempUser);

			/*
			getString returns the data. Below are patient attributes.
			1 = password		9 = name
			2 = username		10 = address
			3 = pharmacy		11 = email
			4 = birthdate		12 = suspend
			5 = cellphone		13 = ssn
			6 = phone			
			7 = insurance		 
			8 = gender			
			 */ 
		}

		return patientInfo;
	}

	/**
	 * Creates connection to Database
	 * @return
	 * @throws Exception
	 */
	private static Connection connect () throws Exception
	{
		//Accessing driver from the JAR file
		Class.forName("com.mysql.jdbc.Driver");

		//Creating a variable for the connection called "con"
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:8889/CS2340","root","root");
		//jdbc:mysql://localhost:8889/testdb --> database
		//DB user --> root
		//DB password --> root

		//insertStatement.executeUpdate("INSERT INTO names " + "VALUES ('Jimmy', 'johns')");
		return con;
	}


	/**
	 *--------------------------------------------------------------------------------------------------------------------
	 * STATIC METHOD -- Will deliver a string of attributes in order.
	 * @param ssn - social security number of a patient needed to access the database
	 * @return patientInfo - a string of all attributes for a particular patient
	 * @throws Exception
	 */
	private static String getUserAttributes(int ssn, int attributeNumber) throws Exception
	{
		System.out.println("Ran getUserAttributes");
		//create the query
		PreparedStatement statement = connect().prepareStatement("SELECT * FROM patient WHERE ssn=" + ssn);

		//Creating a variable to execute query
		ResultSet result = statement.executeQuery();

		String patientInfo = "";

		while(result.next())
		{


			switch (result.getMetaData().getColumnType(attributeNumber)) 
			{
			case java.sql.Types.DATE : 

				DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");

				System.out.println("TESTING: " + result.getDate(attributeNumber));
				patientInfo = formatter.format(result.getDate(attributeNumber)); 
				System.out.println("Date: " + patientInfo);break;

			default : patientInfo = result.getString(attributeNumber); break;
			}
			//			DateFormat formatter;
			//			formatter = new SimpleDateFormat("yyyy-MM-dd");
			//			System.out.println("Check a4:Before try " +formatter);
			/*

			patientInfo=
				result.getString(1) + " " +  result.getString(2) + " " + result.getString(3) + " " + result.getString(4)+ " " + 
				result.getString(5) + " " +  result.getString(6) + " " + result.getString(7) + " " + result.getString(8)+ " " + 
				result.getString(9) + " " +  result.getString(10) + " " + result.getString(11) + " " + result.getString(12)+ " " + 
				result.getString(13);
			getString returns the data. Below are patient attributes.
			1 = password		9 = name
			2 = username		10 = address
			3 = pharmacy		11 = email
			4 = birthdate				12 = suspend
			5 = cellphone		13 = ssn
			6 = phone			
			7 = insurance		 
			8 = gender			
			 */
		}
		return patientInfo;
	}

	/**
	 * --------------------------------------------------------------------------------------------------------------------
	 * Use this method ONLY if you're accessing an attribute with a DATE DATATYPE.
	 * @param ssn
	 * @param attributeNumber
	 * @return
	 * @throws Exception
	 */
	private Date getPatientBday(int ssn, int attributeNumber) throws Exception
	{
		System.out.println("Ran getPatientBday");
		//create the query
		PreparedStatement statement = connect().prepareStatement("SELECT * FROM patient WHERE ssn=" + ssn);

		//Creating a variable to execute query
		ResultSet result = statement.executeQuery();

		Date patientInfo = null;

		while(result.next())
		{
			switch (result.getMetaData().getColumnType(attributeNumber)) 
			{
			case java.sql.Types.DATE : patientInfo = result.getDate(attributeNumber); break;
			default : patientInfo = null; break;
			}

		}
		return patientInfo;
	}


	/**
	 * --------------------------------------------------------------------------------------------------------------------
	 * Saves Patient informatin into database for the first time
	 * Employee will call this method to save patient information
	 * @param User - Employee must provide social security number 
	 * @throws Exception
	 */
	public boolean addUser(User user) 
	{
		System.out.println("Ran addUser");
		Statement stmt = null;

		/**/
		String password = user.getPassword();
		String username = user.getUserName();
		String pharmacy = user.getPharmacy();
		Date birthdate = user.getBirthDate();
		System.out.println("Birthdate: "+birthdate);
		String cellphone = user.getCellPhone();
		String phone = user.getPhone();
		String insurance = user.getInsurance();
		Gender gender = user.getGender();
		String name = user.getName();
		String address = user.getAddress();
		String email = user.getEmail();
		boolean suspended = user.getSuspended();
		int ssn = user.getSSN();
		/*
		INSERT INTO `CS2340`.`patient` (`password`, `username`, `pharmacy`, `birthdate`, `cellphone`, 
				`phone`, `insurance`, `gender`, `name`, `address`, `email`, `suspended`, `ssn`) 
				VALUES ('c', 'd', NULL, NULL, NULL, NULL, NULL, 'N/A', '', NULL, '', 'true', '');
		 */
		System.out.print("INSERT INTO `CS2340`.`patient` " +
				"(`password`, `username`, `pharmacy`, `birthdate`, `cellphone`, `phone`, `insurance`, `gender`, `name`, `address`, " +
				"`email`, `suspended`, `ssn`) " +
				"VALUES " +
				"(`"+password+"`, `"+username+"`, `"+pharmacy+"`, `"+birthdate+"`, `"+cellphone+"`, `"+phone+"`, `"+insurance+"`, `"+gender+"`, `"+name+"`, `"+address+"`, " +
				"`"+email+"`, `"+suspended+"`, `"+ssn+"`);");

		try {
			stmt = connect().createStatement();

			stmt.executeUpdate("INSERT INTO `CS2340`.`patient` " +
					"(`password`, `username`, `pharmacy`, `birthdate`, `cellphone`, `phone`, `insurance`, `gender`, `name`, `address`, `email`, `suspended`, `ssn`) " +
					"VALUES " +
					"('"+password+"', '"+username+"', '"+pharmacy+"', '"+birthdate+"', '"+cellphone+"', '"+phone+"', '"+insurance+"', '"+gender+"', '"+name+"', '"+address+"', '"+email+"', '"+suspended+"', '"+ssn+"');");

			/*
						getString returns the data. Below are patient attributes.
						1 = password		9 = name
						2 = username		10 = address
						3 = pharmacy		11 = email
						4 = birthdate		12 = suspend
						5 = cellphone		13 = ssn
						6 = phone			
						7 = insurance		 
						8 = gender			
			 */
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			e.getClass();
			System.out.println("SQL Exception, Check birthdate datatype, it must be of the form 'yyyy-MM-dd'");
			return false;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} 



	}

	/**
	 * --------------------------------------------------------------------------------------------------------------------
	 * Use this method to update a users information
	 * @param ssn - must use SSN number when using this method
	 * @throws SQLException
	 * @throws Exception
	 */
	public boolean updateUser(User user, int ssn) throws SQLException,Exception
	{
		System.out.println("Ran updateUser");
		Statement stmt = null;

		/**/
		String password = user.getPassword();
		//String username = user.getUserName();
		String pharmacy = user.getPharmacy();
		Date birthdate = user.getBirthDate();
		String cellphone = user.getCellPhone();
		String phone = user.getPhone();
		String insurance = user.getInsurance();
		Gender gender = user.getGender();
		String name = user.getName();
		String address = user.getAddress();
		String email = user.getEmail();
		//Boolean suspended = user.getSuspended();



		/*
				UPDATE  `CS2340`.`patient` SET  `password` =  'testfgfg',
				`username` =  'testfg',
				`pharmacy` =  'testfg',
				`cellphone` =  'fg',
				`phone` =  'fg',
				`insurance` =  'teser',
				`gender` =  'FEMALE',
				`name` =  'testgfg',
				`address` =  'testfg',
				`email` =  'testggf',
				`suspended` =  '0',

				Table Attribute List:
					getString returns the data. Below are patient attributes.
					1 = password		9 = name
					2 = username		10 = address
					3 = pharmacy		11 = email
					4 = birthdate				12 = suspend
					5 = cellphone		13 = ssn
					6 = phone			
					7 = insurance		 
					8 = gender	


UPDATE  `CS2340`.`patient` SET  `password` =  'cu',
`username` =  'uu',
`pharmacy` =  'u',
`birthdate` =  '2011-03-14',
`cellphone` =  'qw',
`phone` =  'qw',
`insurance` =  'qw',
`gender` =  'Female',
`name` =  'qw',
`address` =  'qw',
`email` =  'rrrrrrr'
WHERE `patient`.`ssn` = 0

	"UPDATE `CS2340`.`patient` SET " +
					" `password` = '"+password+"', " +
					"`username` = '"+username+"', " +
					"`pharmacy` = '"+pharmacy+"', " +
					"`birthdate` = '"+bi+"', " +
					"`cellphone` = '"+cellphone+"', " +
					"`phone` = '"+phone+"', " +
					"`insurance` = '"+insurance+"', " +
					"`gender` = '"+gender+"', " +
					"`name` = '"+name+"', " +
					"`address` = '"+address+"`, " +
					"`email` = '"+email+"', " +
					"`suspended` = '"+suspended+"', " +
					"`ssn` = '"+ssn+"' WHERE `patient`.`ssn` ="+ssn	

		 */
		try{
			stmt = connect().createStatement();
			stmt.executeUpdate
			(
					"UPDATE  `CS2340`.`patient` SET  " +
					"`password` =  '"+password+"', " +
					"`username` =  '"+password+"', " +
					"`pharmacy` =  '"+pharmacy+"'," +
					"`birthdate` =  '"+birthdate+"'," +
					"`cellphone` =  '"+cellphone+"'," +
					"`phone` =  '"+phone+"'," +
					"`insurance` =  '"+insurance+"'," +
					"`gender` =  '"+gender+"'," +
					"`name` =  '"+name+"'," +
					"`address` =  '"+address+"'," +
					"`email` =  '"+email+"'" +
					"WHERE `patient`.`ssn` = " + 
					ssn	
			);
		}
		finally{
			stmt.close();
		}
		return true;
	}

	/**
	 * --------------------------------------------------------------------------------------------------------------------
	 * STATIC METHOD -- Deletes a user never to return again.
	 * @param ssn - must specify user by SSN
	 * @throws SQLException
	 * @throws Exception
	 */
	public boolean deleteUser(int ssn) throws SQLException,Exception
	{
		System.out.println("Ran deleteUser");
		Statement stmt = null;
		boolean confirmation = true;

		/*
		DELETE FROM `CS2340`.`patient` WHERE `patient`.`ssn` = 123456789
		 SELECT * FROM `patient` WHERE 1
		 DELETE FROM `CS2340`.`patient` WHERE `patient`.`ssn` = 234540987
		 */
		try
		{ 
			stmt = connect().createStatement();
			stmt.executeUpdate("DELETE FROM `CS2340`.`patient` WHERE `patient`.`ssn` = " + ssn);
			return confirmation;
		}
		finally
		{
			stmt.close();
		}

	}

	/**
	 * --------------------------------------------------------------------------------------------------------------------
	 * @param password
	 * @param username
	 * @return
	 * @throws Exception
	 */
	public User authenticateUser(String username, String password) throws Exception
	{
		System.out.println("Ran authenticateUser");
		/*create the query*/

		PreparedStatement statement = connect().prepareStatement("SELECT * FROM `patient` WHERE `username` LIKE '"+username+"'");

		/*Creating a variable to execute query*/
		ResultSet result = statement.executeQuery();

		String dbPassword = "";

		while(result.next())
		{
			dbPassword = result.getString(1);

			/*
			getString returns the data. Below are patient attributes.
			1 = password		9 = name
			2 = username		10 = address
			3 = pharmacy		11 = email
			4 = birthdate				12 = suspend
			5 = cellphone		13 = ssn
			6 = phone			
			7 = insurance		 
			8 = gender			
			 */ 
		}

		/* Create User */

		if (password.equals(dbPassword)) /* Password Success */
		{
			System.out.println("Query authenticateUser -- Login Successful"); 
			return createUser(username);
		}
		else /* Password Fail */
		{
			System.out.println("Query authenticateUser -- Login Failed -- returning 'No User' user");
			return createUser("No User");
		}
	}

	/**
	 * --------------------------------------------------------------------------------------------------------------------
	 * Use this method to return a user matching the SSN provided
	 * @param ssn
	 * @return User -- The User class will be returned with all its attributes gathered from the database
	 * @throws Exception
	 */
	public User loadUser(int ssn) throws Exception
	{
		System.out.println("Ran loadUser");
		PreparedStatement statement = connect().prepareStatement("SELECT * FROM `patient` WHERE `ssn` LIKE '"+ssn+"'");

		/*Creating a variable to execute query*/
		ResultSet result = statement.executeQuery();
		String dbSSN = "";

		while(result.next())
		{
			dbSSN=result.getString(2);

			/*
		getString returns the data. Below are patient attributes.
		1 = password		9 = name
		2 = username		10 = address
		3 = pharmacy		11 = email
		4 = birthdate				12 = suspend
		5 = cellphone		13 = ssn
		6 = phone			
		7 = insurance		 
		8 = gender			
			 */ 
		}

		return createUser(dbSSN);

	}

	/**
	 * --------------------------------------------------------------------------------------------------------------------
	 * You must specify the username first.
	 * Usernames are unique
	 * @param username
	 * @return int -- this will be the patients SSN with no dashes or spaces between the numbers
	 * @throws SQLException
	 * @throws Exception
	 */
	public int getSSN(String username) throws SQLException, Exception
	{
		System.out.println("Ran getSSN");
		//create the query
		PreparedStatement statement = connect().prepareStatement("SELECT * FROM `patient` WHERE `username` LIKE '"+username+"'");


		//Creating a variable to execute query
		ResultSet result = statement.executeQuery();

		int socialNum = 0;

		while(result.next())
		{
			socialNum = Integer.parseInt(result.getString(13));
			/*
				getString returns the data. Below are patient attributes.
				1 = password		9 = name
				2 = username		10 = address
				3 = pharmacy		11 = email
				4 = birthdate				12 = suspend
				5 = cellphone		13 = ssn
				6 = phone			
				7 = insurance		 
				8 = gender			
			 */

		}

		return socialNum;
	}

	/**
	 * --------------------------------------------------------------------------------------------------------------------
	 * Creates a User object. 
	 * Use this method when accessing User Information from the database
	 * A Nurse or Doctor will use this method whenever he pulls up information on a patient
	 * @param username
	 * @return User
	 * @throws Exception 
	 */
	private User createUser(String username) throws Exception
	{
		System.out.println("Ran createUser");
		User user = new User();
		int tempSSN = 0;

		tempSSN = getSSN(username);

		String a1=getUserAttributes(tempSSN, 1);
		String a2=getUserAttributes(tempSSN, 2);
		String a3=getUserAttributes(tempSSN, 3);
		Date a4=getPatientBday(tempSSN, 4);
		String a5=getUserAttributes(tempSSN, 5);
		String a6=getUserAttributes(tempSSN, 6);
		String a7=getUserAttributes(tempSSN, 7);
		String a8=getUserAttributes(tempSSN, 8);
		String a9=getUserAttributes(tempSSN, 9);
		String a10=getUserAttributes(tempSSN, 10);
		String a11=getUserAttributes(tempSSN, 11);
		String a12=getUserAttributes(tempSSN, 12);
		String a13=getUserAttributes(tempSSN, 13);

		user.setPassword(a1);
		user.setUserName(a2);
		user.setPharmacy(a3);

		/*Convert String date to date*/

		//				System.out.println("Check Temporary " +a4);
		//				DateFormat formatter;
		//				formatter = new SimpleDateFormat("yyyy-MM-dd");
		//				System.out.println("Check a4:Before try " +formatter);

		//		bday =formatter.;
		//		System.out.println("Check bday: " + bday);
		//		//user.setBirthDate(bday);
		//		System.out.println("Check try: " + bday);


		user.setBirthDate(a4);
		user.setCellPhone(a5);
		user.setPhone(a6);
		user.setInsurance(a7);
		/*Convert String enum to true java Enum*/
		/*System.out.print("print a8: " +a8);*/
		if (a8.equals("Male"))
		{
			user.setGender(Gender.MALE);
		}
		else user.setGender(Gender.FEMALE);

		user.setName(a9);
		user.setAddress(a10);
		user.setEmail(a11);

		user.setSuspended(convertStringToBoolean(a12));
		System.out.println("If SSN is '111111111', then user login information failed: SSN is " + a13);
		user.setSSN(Integer.parseInt(a13));

		return  user;
		/*
		getString returns the data. Below are patient attributes.
		1 = password		9 = name
		2 = username		10 = address
		3 = pharmacy		11 = email
		4 = birthdate		12 = suspend
		5 = cellphone		13 = ssn
		6 = phone			
		7 = insurance		 
		8 = gender			
		 */
	}
	
	private static String getAppAttributes(int ssn, int attributeNumber) throws Exception
	{
		System.out.println("Ran getAppAttributes");
		//create the query
		PreparedStatement statement = connect().prepareStatement("SELECT * FROM appointment WHERE ssn=" + ssn);

		//Creating a variable to execute query
		ResultSet result = statement.executeQuery();

		String appInfo = "";

		while(result.next())
		{
			switch (result.getMetaData().getColumnType(attributeNumber)) 
			{
			case java.sql.Types.DATE : 

				DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");

				System.out.println("TESTING: " + result.getDate(attributeNumber));
				appInfo = formatter.format(result.getDate(attributeNumber)); 
				System.out.println("Date: " + appInfo);break;

			default : appInfo = result.getString(attributeNumber); break;
			}
		}
		return appInfo;
	}
	
	/**
	 * --------------------------------------------------------------------------------------------------------------------
	 * String to boolean conversion
	 */
	private boolean convertStringToBoolean(String booleanString) {
		System.out.println("Ran convertStringToBoolean");
		String strBoolean = booleanString;

		/*Do the String to boolean conversion*/
		boolean theValue = Boolean.parseBoolean(strBoolean);

		return theValue;
	}

	/**
	 * --------------------------------------------------------------------------------------------------------------------
	 * @param args
	 * @throws Exception
	 */
	public static void main (String[] args) throws Exception
	{MyQuery newquery = new MyQuery();	
	//		MyQuery newquery = new MyQuery();
	//		Boolean newstring = newquery.authenticateUser("ERd","DFG");
	//		System.out.println(newstring);
	//				MyQuery newquery = new MyQuery();
	//				String newstring = newquery.searchUserByName("RTY");
	//				System.out.println(newstring);
	//				MyQuery newquery = new MyQuery();
	//				int newstring = newquery.getSSN("DFG");
	//				System.out.println("Should be SSN: " + newstring);
	//				MyQuery newquery = new MyQuery();
	//				boolean newstring = newquery.authenticateUser("ER","DFG");
	//				System.out.println("Should be SSN: " + newstring);
	//		MyQuery newquery = new MyQuery();
	//		User newuser = newquery.createUser("blue34");
	//		System.out.println(newuser.getPassword() + " " + newuser.getUserName() + " " + newuser.getPharmacy() + " " + newuser.getBirthDate() + " " + newuser.getCellPhone() + " " + newuser.getPhone() + " " + newuser.getInsurance() + " " + newuser.getGender() + " " + newuser.getName() + " " + newuser.getAddress() + " " + newuser.getEmail() + " " + newuser.getSuspended() + " " + newuser.getSSN());
//					MyQuery newquery = new MyQuery();
	//				getUserAttributes(123784567, 4);

	//		Patient test = MyQuery.getPatient("rugby57");
	//		System.out.print(test.getUsername());
	//		
//	User newuser = newquery.authenticateUser("z", "d");
//	System.out.println("User Name: " + newuser.getName());
	//		newquery.deleteUser(123456789);
//	newquery.updateAppointment

	}
	//Save Apppointments and Patient Invoices 
	/*
	BOOLEAN delete appointment()
		Apppointment loadAppointment()
		boolean addAppointment(Appointments app) //
		searchUserAppointments
	boolean updateAppointment(Appointment app)
	 */

	

	
	/**
	 * -----------------------------------------------------------------------------------------------------Need to be tested for Time
	 * Use this method ONLY if you're accessing an attribute with a DATE DATATYPE.
	 * @param ssn
	 * @param attributeNumber
	 * @return Date
	 * @throws Exception
	 */
	private Date getAppDate(int ssn, int attributeNumber) throws Exception
	{
		System.out.println("Ran getAppDate");
		//create the query
		PreparedStatement statement = connect().prepareStatement("SELECT * FROM appointment WHERE ssn=" + ssn);

		//Creating a variable to execute query
		ResultSet result = statement.executeQuery();

		Date date = null;

		while(result.next())
		{
			switch (result.getMetaData().getColumnType(attributeNumber)) 
			{
			case java.sql.Types.DATE : date = result.getDate(attributeNumber); break;
			default : date = null; break;
			}

		}
		return date;
	}
	
	/**
	 * -----------------------------------------------------------------------------------------------------Need to be tested for Time
	 * Use this to access a particular appointment
	 * @param ssn
	 * @param date
	 * @param user
	 * @return Appointment
	 * @throws SQLException
	 * @throws Exception
	 */
	public Appointment loadAppointment(int ssn, Date date) throws SQLException, Exception
	{
		System.out.println("Ran loadAppointment");
		/*SELECT * FROM `appointment` WHERE `ssn`='111111111'AND `date`='0000-00-00'*/
		PreparedStatement statement = connect().prepareStatement("SELECT * FROM `appointment` WHERE `ssn` = '"+ssn+"' AND `date` = '"+date+"'");

		/*Creating a variable to execute query*/					
		ResultSet result = statement.executeQuery();
		String dbSSN = "";

		while(result.next())
		{
			dbSSN = result.getString(1);
			 
			/*
			Below are appointment attributes.
			1 = ssn			5 = reason
			2 = date		6 = name
			3 = timeStart	7 = timeEnd
			4 = doctor		8 = patient
			 */
		}

		
		return createAppointment(dbSSN);
	}
	/**
	 * Use this method when you have a name and need all the appointments associated with this name in an Arraylist
	 * @param name
	 * @return ArrayList<Appointment>
	 * @throws Exception
	 */
	public ArrayList<Appointment> searchUserAppointments(String name) throws Exception
	{
		System.out.println("SELECT * FROM `CS2340`.`appointment` WHERE `name` LIKE " + "'"+name+"' LIMIT 0 , 30");
		//create the query
		PreparedStatement statement = connect().prepareStatement("SELECT * FROM `CS2340`.`appointment` WHERE `name` LIKE " + "'"+name+"' LIMIT 0 , 30");

		//Creating a variable to execute query					
		ResultSet result = statement.executeQuery();
		System.out.println(result);
		// constructs a new empty ArrayList  

		ArrayList<Appointment> appInfo = new ArrayList<Appointment>();

		while(result.next())
		{
			Appointment tempApp = createAppointment(result.getString(6));
			appInfo.add(tempApp);
			/*
			Below are appointment attributes.
			1 = ssn			5 = reason
			2 = date		6 = name
			3 = timeStart	7 = timeEnd
			4 = doctor		8 = patient
			 */
		}
		return appInfo;
	}
	
	/**
	 * -----------------------------------------------------------------------------------------------------Need to be tested for Time
	 * Use this method ONLY if you're accessing an attribute with a TIME DATATYPE.
	 * @param ssn
	 * @param attributeNumber
	 * @return
	 * @throws Exception
	 */
	private Time getAppTime(int ssn, int attributeNumber) throws Exception
	{
		System.out.println("Ran getAppTime");
		//create the query
		PreparedStatement statement = connect().prepareStatement("SELECT * FROM appointment WHERE ssn=" + ssn);

		//Creating a variable to execute query
		ResultSet result = statement.executeQuery();

		Time time = null;

		while(result.next())
		{
			switch (result.getMetaData().getColumnType(attributeNumber)) 
			{
			case java.sql.Types.TIME : time = result.getTime(attributeNumber); break;
			default : time = null; break;
			}

		}
		return time;
	}
	
	/**
	 * -----------------------------------------------------------------------------------------------------Need to be tested
	 * Create one appointment instance for you
	 * @param username
	 * @param date
	 * @param user
	 * @return
	 * @throws Exception
	 */
	private Appointment createAppointment(String username) throws Exception
	{
		System.out.println("Ran createAppointment");
		
		int tempSSN = 0;
		
		tempSSN = getSSN(username);

		Date a2=getAppDate(tempSSN, 2); /*date*/
		Time a3=getAppTime(tempSSN,3);  /*timeStart*/
		String a4=getAppAttributes(tempSSN, 4);  /*doctor*/
		String a5=getAppAttributes(tempSSN, 5);  /*reason*/
		String a6=getAppAttributes(tempSSN, 6);	 /*name*/
		Time a7=getAppTime(tempSSN,7);	 /*timeEnd*/
		String a8=getAppAttributes(tempSSN, 8);	 /*patient*/
		
		Appointment app = new Appointment(a2,a3,a7);
		app.setDate(a2);
		app.setTimeStart(a3);
		app.setDoctor(a4);
		app.setReason(a5);
		app.setPatient(a6);
		app.setTimeEnd(a7);
		app.setPatient(a8);

		return  app;
	}

	/**
	 * -----------------------------------------------------------------------------------------------------Need to be tested for Time
	 * Use this method to add a new appointment. 
	 * User parameter must have a SSN and Name in the database.
	 * Patient must already have a user profile in the database.
	 * @param user - user instance
	 * @param app - appointment instance
	 * @return boolean - true for success appointment addition to database
	 */
	public boolean addAppointment(User user, Appointment app) 
	{
		System.out.println("Ran addAppointment");
		Statement stmt = null;

		/**/
		int ssn = user.getSSN();
		Date appDate = app.getDate();
		//Date appDate = user.getBirthDate();
		Time timeStart = app.getTimeStart();
		String doctor = app.getDoctor();
		String reason = app.getReason();
		String name = app.getPatient();
		/*String name = "John";*/ 
		Time timeEnd = app.getTimeEnd();
		String patient = app.getPatient();

		System.out.print("INSERT INTO `CS2340`.`appointment` " +
				"(`ssn`, `date`, `timeStart`, `doctor`, `reason`, `name`, `timeEnd`, `patient`) " +
				"VALUES " +
				"('"+ssn+"', '"+appDate+"', '"+timeStart+"', '"+doctor+"', '"+reason+"', '"+name+"', '"+timeEnd+"', '"+patient+"');");


		try {
			stmt = connect().createStatement();

			stmt.executeUpdate("INSERT INTO `CS2340`.`appointment` " +
					"(`ssn`, `date`, `timeStart`, `doctor`, `reason`, `name`, `timeEnd`, `patient`) " +
					"VALUES " +
					"('"+ssn+"', '"+appDate+"', '"+timeStart+"', '"+doctor+"', '"+reason+"', '"+name+"', '"+timeEnd+"', '"+patient+"');");

			/*
						Below are appointment attributes.
						1 = ssn			5 = reason
						2 = date		6 = name
						3 = timeStart	7 = timeEnd
						4 = doctor		8 = patient

			 */
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			e.getClass();
			System.out.println("SQL Exception in addAppointment");
			return false;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} 	
	}
	
	/**
	 * Simply deletes an appointment, must give date and ssn
	 * @param ssn
	 * @param date
	 * @return boolean True if the appointment was deleted
	 * @throws SQLException
	 * @throws Exception
	 */
	public boolean deleteAppointment(int ssn, Date date) throws SQLException,Exception
	{
		System.out.println("Ran deleteUser");
		Statement stmt = null;
		boolean confirmation = true;

		/*
		DELETE FROM `CS2340`.`appointment` WHERE `appointment`.`ssn` = '111111111' AND `appointment`.`date` = '0000-00-00'
		 */
		try
		{ 
			stmt = connect().createStatement();
			stmt.executeUpdate("DELETE FROM `CS2340`.`appointment` WHERE `appointment`.`ssn` = '"+ssn+"' AND `appointment`.`date` = '"+date+"'");
			return confirmation;
		}
		finally
		{
			stmt.close();
		}

	}
	
	/**
	 * Update an appointment using a user and the appointment instance
	 * @param user
	 * @param app
	 * @return boolean - true if it worked
	 * @throws SQLException
	 * @throws Exception
	 */
	public boolean updateAppointment(User user, Appointment app) throws SQLException,Exception
	{
		System.out.println("Ran updateUser");
		Statement stmt = null;

		int ssn = user.getSSN();
		Date appDate = app.getDate();
		//Date appDate = user.getBirthDate();
		Time timeStart = app.getTimeStart();
		String doctor = app.getDoctor();
		String reason = app.getReason();
		String name = app.getPatient();
		/*String name = "John";*/ 
		Time timeEnd = app.getTimeEnd();
		String patient = app.getPatient();

		try{
			stmt = connect().createStatement();
			stmt.executeUpdate
			(
					"UPDATE  `CS2340`.`appointment` SET  " +
					"`ssn` =  '"+ssn+"', " +
					"`date` =  '"+appDate+"', " +
					"`timeStart` =  '"+timeStart+"'," +
					"`doctor` =  '"+doctor+"'," +
					"`reason` =  '"+reason+"'," +
					"`name` =  '"+name+"'," +
					"`timeEnd` =  '"+timeEnd+"'," +
					"`patient` =  '"+patient+"'," +
					"WHERE `appointment`.`ssn` = " + ssn +
					"AND `appointment`.`date` = " + appDate
			);
			return true;
		}
		finally{
			stmt.close();
		}
		
	}
}






